/*DONE* Table [dbo].[<CompanyName>$LSC Dining Table Status$5ecfc871-5d82-43f1-9c54-59685e82318d]  1/1  Script Date: 14.1.2021 - JI ******/
/*Add prestaging tables for all companies in LSInsight$Companies table*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DECLARE @CreateTable as VARCHAR(MAX)
DECLARE @Company as VARCHAR(100)
DECLARE @CompanyCounter as INT
DECLARE @i as INT

SET @i = 0
SET @CompanyCounter = (SELECT
		MAX([Company])
	FROM LSInsight$Companies)

WHILE @i <= @CompanyCounter 
BEGIN
SET @Company = (SELECT
		LSInsight$Companies.[CompanyPrefix]
	FROM dbo.LSInsight$Companies
	WHERE [Company] = @i)

IF @Company IS NOT NULL
BEGIN
DECLARE @DropTable as VARCHAR(MAX)
SET @DropTable = 'DROP TABLE IF EXISTS [dbo].[' + @Company + '$LSC Dining Table Status$5ecfc871-5d82-43f1-9c54-59685e82318d]'
EXEC (@DropTable)

SET @CreateTable = 'CREATE TABLE [dbo].[' + @Company + '$LSC Dining Table Status$5ecfc871-5d82-43f1-9c54-59685e82318d](
	[timestamp] [timestamp] NOT NULL,
	[Dining Area ID] [nvarchar](20) NOT NULL,
	[Dining Table No_] [int] NOT NULL,
	[Default Section Code] [nvarchar](20) NOT NULL,
	[Dining Table Status] [int] NOT NULL,
	[No_ of Seated Guests] [int] NOT NULL,
	[No_ of Guests (No Trans_)] [int] NOT NULL,
	[Staff Name (No Trans_)] [nvarchar](30) NOT NULL,
	[Active Order] [nvarchar](20) NOT NULL,
	[Info Requested] [tinyint] NOT NULL,
	[Dining Table Type ID] [nvarchar](20) NOT NULL,
	[Owner Staff ID] [nvarchar](20) NOT NULL,
	[Guest ID] [nvarchar](100) NOT NULL,
	[Date-Time Seated] [datetime] NOT NULL,
	[Date-Time Order Created] [datetime] NOT NULL,
	[Member Card No_] [nvarchar](100) NOT NULL,
	[Button Staff Text] [nvarchar](30) NOT NULL,
	[Button Time Text] [nvarchar](30) NOT NULL,
	[Button Guest Text] [nvarchar](30) NOT NULL,
	[Button Amt_ Text] [nvarchar](30) NOT NULL,
	[Button Status Text] [nvarchar](30) NOT NULL,
	[Button Order No_ Text] [nvarchar](30) NOT NULL,
	[Button Guest ID Text] [nvarchar](100) NOT NULL,
	[Button Reserved Text] [nvarchar](30) NOT NULL,
	[Pop-up Header Text] [nvarchar](100) NOT NULL,
	[Pop-up Alert Text] [nvarchar](100) NOT NULL,
	[Pop-up Order No_ Text] [nvarchar](50) NOT NULL,
	[Pop-up Bill Printed No_ Text] [nvarchar](50) NOT NULL,
	[Pop-up Kot No_ Text] [nvarchar](50) NOT NULL,
	[Pop-up Occup_ Chg_ Status Text] [nvarchar](100) NOT NULL,
	[Pop-up Change Action Text] [nvarchar](100) NOT NULL,
	[Caption Text] [nvarchar](80) NOT NULL,
	[Cover Count] [int] NOT NULL,
	[Seating Capacity] [int] NOT NULL,
	[Bill Printed] [tinyint] NOT NULL,
	[Order Paid] [tinyint] NOT NULL,
	[Active Alert] [tinyint] NOT NULL,
	[Table Status Text] [nvarchar](100) NOT NULL,
	[Active KOT Menu Type Code] [nvarchar](10) NOT NULL,
	[Infobar Status Text] [nvarchar](250) NOT NULL,
	[Seating Capacity Text] [nvarchar](30) NOT NULL,
	[Total No_ of KOTs] [int] NOT NULL,
	[Expected Dining Duration] [int] NOT NULL,
	[Requested Cover] [int] NOT NULL,
	[Next Reserved Date Time] [datetime] NOT NULL,
	[Reservation No_] [nvarchar](20) NOT NULL,
	[Next Reservation No_] [nvarchar](20) NOT NULL,
	[Next Reservation Name] [nvarchar](100) NOT NULL,
	[Seat Reserved Guests] [tinyint] NOT NULL,
	[GUI Status] [int] NOT NULL,
	[Main Status] [int] NOT NULL,
	[Joining Info] [nvarchar](50) NOT NULL,
	[Table Is Master] [tinyint] NOT NULL,
	[Joined to Master Table] [int] NOT NULL,
	[Period Dining Duration Code] [nvarchar](20) NOT NULL,
	[Reservation_Allocation in Use] [tinyint] NOT NULL,
	[Show Allocated Border] [tinyint] NOT NULL,
	[$systemId] [uniqueidentifier] NOT NULL,
	[$systemCreatedAt] [datetime] NOT NULL,
	[$systemCreatedBy] [uniqueidentifier] NOT NULL,
	[$systemModifiedAt] [datetime] NOT NULL,
	[$systemModifiedBy] [uniqueidentifier] NOT NULL,
 CONSTRAINT [' + @Company + '$LSC Dining Table Status$5ecfc871-5d82-43f1-9c54-59685e82318d$Key1] PRIMARY KEY CLUSTERED 
(
	[Dining Area ID] ASC,
	[Dining Table No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [' + @Company + '$LSC Dining Table Status$5ecfc871-5d82-43f1-9c54-59685e82318d$$systemId] UNIQUE NONCLUSTERED 
(
	[$systemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]'

EXEC(@CreateTable)

END
SET @i = @i + 1
END
